﻿Imports System.Environment
Imports System.IO
Imports System.Text

Public Class frmExport

    Private rs As ADODB.Recordset
    Private szSql As String

    ' Enregistrement ECRITURE (79)
    ' TYPE            1
    ' COMPTE          8
    ' JNL             2
    ' FOLIO           3 '001'
    ' JJ              2
    ' MM              2
    ' YY              2
    ' F               1
    ' LIBELLE         20
    ' SENS            1 (C/D)
    ' SIGNE           1 (+/-)
    ' MONTANT         12
    ' F               19
    ' NOPIECE         5

    ' Enregistrement COMPTE (218)
    ' TYPE            1
    ' COMPTE          8
    ' LIBELLE         30
    ' CLE             7
    ' F               52
    ' COMPTE COLL     8
    ' F               110
    ' FLAG            1
    ' TYPE CPTE       1

    Dim JNL_VENTE As String
    Dim nFich As Integer
    ' Tampon String intermédiare
    Dim sb As New StringBuilder()
    Dim buf As String
    Dim champ As String
    Dim curTropPercu As Decimal
    Dim curTotalRemise As Decimal
    Dim LModeRegl As Integer
    Dim LLibRegl As String
    Dim LJNL As String
    Dim LDate As Date
    Dim LCompte As Integer

    Private Sub frmExport_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        ' lire Société
        szSql = "CALL spGET_P_SOCIETE('" & gSocieteCode & "')"
        GetRecordset(ctDSN_DEVFAC, szSql, rs)
        If rs.RecordCount < 1 Then
            MsgBox("Table P_SOCIETE non trouvé ....")
            rs.Close()
            Exit Sub
        End If
        ' memoriser les code journaux
        JNL_VENTE = rs.Fields("v_code").Value
        TE_STATUS.Text = ""
        TE_DEBUT.Value = DateAdd("d", 1, rs.Fields("c_dern").Value)
        TE_FIN.Value = Now()
        TE_FICHIER_EXPORT.Text = getDossierRoot() & "EXPORT\COMPTA-" & gSocieteCode & ".TXT"

    End Sub

    Private Sub BO_EXPORT_Click(sender As System.Object, e As System.EventArgs) Handles BO_EXPORT.Click

        If IsUserAuthorised(ctCompta) = False Then
            Exit Sub
        End If
        ' controles DATES
        If DateDiff("d", TE_DEBUT.Value, TE_FIN.Value) < 0 Then
            MsgBox("Date de fin doit supérieure à la date de début")
            TE_DEBUT.Focus()
            Exit Sub
        End If

        Me.Cursor = System.Windows.Forms.Cursors.WaitCursor

        nFich = FreeFile()

        LJNL = ""
        LDate = CDate("1980/01/01")
        LModeRegl = 0
        curTotalRemise = 0
        ' Générer journal Versement
        szSql = "CALL spGET_LIV_BNQ('byDates','" & gSocieteCode & "',0,0,'" & Format(TE_DEBUT.Value, "yyyy-MM-dd") & _
                "','" & Format(TE_FIN.Value, "yyyy-MM-dd") & "')"
        GetRecordset(ctDSN_DEVFAC, szSql, rs)

        Do Until rs.EOF
            TE_STATUS.Text = "Banque " & rs.Fields("Date").Value & " " & rs.Fields("ETS").Value

            ' rupture sur JNL / DATE / MODEREGL
            If LJNL <> rs.Fields("JNL").Value Or LModeRegl <> rs.Fields("MODEREG").Value Or LDate <> rs.Fields("Date").Value Then
                ' 1ere fois
                If LJNL <> "" Then
                    ' Créer un ligne Remise
                    GenererRemise()
                End If
                LJNL = rs.Fields("JNL").Value
                LModeRegl = rs.Fields("MODEREG").Value
                LLibRegl = rs.Fields("lib_regl").Value
                LDate = rs.Fields("Date").Value
                curTotalRemise = 0
            End If

            ' cumuler les remises
            curTotalRemise = curTotalRemise + rs.Fields("MontantRegl").Value
            ' Créer un ligne ECRITURE
            GenererBANQUE()
            ' Générer Trop Percu éventuel
            curTropPercu = rs.Fields("MontantRegl").Value - rs.Fields("MontantDebit").Value
            If curTropPercu > 0 And curTropPercu < 5 Then
                GenererTropPercu()
            End If
            rs.MoveNext()
        Loop
        If LJNL <> "" Then
            ' Créer la dernière ligne Remise
            GenererRemise()
        End If
        rs.Close()

        ' RAZ les montants de la table des TVA
        ExecQuery(ctDSN_DEVFAC, "CALL spUPD_P_TVA_CUMUL_RAZ()")

        ' Générer Journal Vente
        szSql = "CALL spGET_FACTURE_byDates('" & gSocieteCode & "','" & Format(TE_DEBUT.Value, "yyyy-MM-dd") & _
                "','" & Format(TE_FIN.Value, "yyyy-MM-dd") & "')"
        GetRecordset(ctDSN_DEVFAC, szSql, rs)

        Do Until rs.EOF
            TE_STATUS.Text = "Facture " & rs.Fields("Date").Value & " " & rs.Fields("nomcli").Value

            If rs.Fields("TotalTTC").Value <> 0 Then
                GenererVENTES()
            Else
                MsgBox("Attention ! Facture avec montant à zéro : " & Format(rs.Fields("NO_ID").Value, "000000"))
            End If
            rs.MoveNext()
        Loop
        rs.Close()

        ' Générer TVA ENCAISSE
        szSql = "CALL spGET_P_TVA_byCODE(0)"
        Call GetRecordset(ctDSN_DEVFAC, szSql, rs)
        Do Until rs.EOF
            ' Créer un ligne TVA
            GenererTVA_Coll()
            rs.MoveNext()
        Loop

        Me.Cursor = System.Windows.Forms.Cursors.Default
        TE_STATUS.Text = "La génération du fichier d'export est terminée."
        ' écrire le tampon dans le fichier de sortie
        Using sr As New StreamWriter(TE_FICHIER_EXPORT.Text, False, System.Text.Encoding.Default)
            sr.Write(sb.ToString())
        End Using

        ' mémoriser la date de dernier export
        szSql = "CALL spUPD_P_SOCIETE_DATEEXPORT('" & gSocieteCode & "','" & Format(TE_FIN.Value, "yyyy/MM/dd") & "')"
        ExecQuery(ctDSN_DEVFAC, szSql)

    End Sub

    Private Sub GenererBANQUE()
        buf = "M"
        buf = buf & "01" & Format(rs.Fields("NOCOMPTE").Value, "000000")
        buf = buf & rs.Fields("JNL").Value
        buf = buf & "001"
        buf = buf & Date2JJMMAA(rs.Fields("Date").Value.ToString) 'JJMMAA
        buf = buf & " "

        champ = Trim(rs.Fields("lib_regl").Value) & " " & Trim(rs.Fields("ETS").Value) & " " & Trim(rs.Fields("jst").Value)
        buf = buf & SetLength(champ, 20)
        If rs.Fields("MontantRegl").Value >= 0 Then
            buf = buf & "C+"
            buf = buf & Format(rs.Fields("MontantRegl").Value * 100, "000000000000")
        Else
            buf = buf & "C"
            buf = buf & Format(rs.Fields("MontantRegl").Value * 100, "000000000000")
        End If
        buf = buf & Space(19)
        buf = buf & "00000"
        ' ecrire la ligne 
        sb.AppendLine(buf)

        ' Créer un ligne COMPTE TIERS
        GenererTIERS()

    End Sub

    Private Sub GenererRemise()

        buf = "M"
        buf = buf & Format(LCompte, "00000000")
        buf = buf & LJNL
        buf = buf & "001"
        buf = buf & Date2JJMMAA(LDate.ToString) 'JJMMAA
        buf = buf & " "

        champ = "REMISE " & LLibRegl
        buf = buf & SetLength(champ, 20)
        buf = buf & "D+"
        buf = buf & Format(curTotalRemise * 100, "000000000000")
        buf = buf & Space(19)
        buf = buf & "00000"
        ' ecrire la ligne 
        sb.AppendLine(buf)

    End Sub
    Private Sub GenererTropPercu()

        buf = "M"
        buf = buf & "01" & Format(rs.fields("NOCOMPTE").Value, "000000")
        buf = buf & rs.Fields("JNL").Value
        buf = buf & "001"
        buf = buf & Date2JJMMAA(rs.Fields("Date").Value.ToString) 'JJMMAA
        buf = buf & " "

        champ = "TROP PERCU: " & rs.Fields("nom").Value
        buf = buf & SetLength(champ, 20)
        buf = buf & "D+"
        buf = buf & Format(curTropPercu * 100, "000000000000")
        buf = buf & Space(19)
        buf = buf & "00000"
        ' ecrire la ligne 
        sb.AppendLine(buf)

        buf = "M"
        buf = buf & "67180000"
        buf = buf & rs.Fields("JNL").Value
        buf = buf & "001"
        buf = buf & Date2JJMMAA(rs.Fields("Date").Value.ToString) 'JJMMAA
        buf = buf & " "

        champ = "TROP PERCU: " & rs.Fields("nom").Value
        buf = buf & SetLength(champ, 20)
        buf = buf & "C+"
        buf = buf & Format(curTropPercu * 100, "000000000000")
        buf = buf & Space(19)
        buf = buf & "00000"
        ' ecrire la ligne 
        sb.AppendLine(buf)

    End Sub
    Private Sub GenererVENTES()

        buf = "M"
        buf = buf & "01" & Format(rs.Fields("NOCOMPTE").Value, "000000")
        buf = buf & JNL_VENTE
        buf = buf & "001"
        buf = buf & Date2JJMMAA(rs.Fields("Date").Value.ToString) 'JJMMAA
        buf = buf & " "

        If rs.Fields("c_nom").Value.ToString.Length = 0 Then
            champ = Mid(rs.Fields("nomcli").Value, 1, 10)
        Else
            champ = Mid(rs.Fields("c_nom").Value, 1, 10)
        End If
        champ = champ & " " & Format(rs.Fields("NO_ID").Value, "000000") & " T" & rs.Fields("codetva").Value
        ' libelle = NNNNNNNNNN 999999 T9
        buf = buf & SetLength(champ, 20)
        If rs.Fields("TotalTTC").Value >= 0 Then
            buf = buf & "D+"
            buf = buf & Format(rs.Fields("TotalTTC").Value * 100, "000000000000")
        Else
            buf = buf & "C+"
            buf = buf & Format(rs.Fields("TotalTTC").Value * -100, "000000000000")
        End If
        buf = buf & Space(19)
        buf = buf & "00000"
        ' ecrire la ligne 
        sb.AppendLine(buf)

        ' cumuler les TVA DEDUCTIBLES
        szSql = "CALL spUPD_P_TVA_CUMUL_TVA(" & rs.Fields("codetva").Value & "," & rs.Fields("TotalTVA").Value & "," & rs.Fields("TotalHT").Value & ")"
        ExecQuery(ctDSN_DEVFAC, szSql)

        ' Créer un ligne COMPTE TIERS
        GenererTIERS()

    End Sub

    Private Sub GenererTIERS()

        buf = "C"
        buf = buf & "01" & Format(rs.Fields("NOCOMPTE").Value, "000000")
        champ = Mid(rs.Fields("nom").Value, 1, 30)
        buf = buf & SetLength(champ, 30)

        champ = Mid(rs.Fields("nom").Value, 1, 7)
        buf = buf & SetLength(champ, 7)
        buf = buf & Space(52)
        buf = buf & "41100000"
        buf = buf & Space(110)
        buf = buf & "2C"
        ' ecrire la ligne 
        sb.AppendLine(buf)

    End Sub

    Private Sub GenererTVA_Coll()

        If rs.Fields("MT_TVA").Value <> 0 Then
            buf = "M"
            buf = buf & rs.Fields("CPT_TVA").Value
            buf = buf & JNL_VENTE
            buf = buf & "001"
            buf = buf & Date2JJMMAA(TE_FIN.Value.ToString) 'JJMMAA
            buf = buf & " "

            champ = "TVA ENCAISSE " & Format(rs.Fields("taux").Value, "0.00")
            buf = buf & SetLength(champ, 20)
            buf = buf & "C"
            If rs.Fields("MT_TVA").Value > 0 Then
                buf = buf & "+"
                buf = buf & Format(rs.Fields("MT_TVA").Value * 100, "000000000000")
            Else
                buf = buf & "-"
                buf = buf & Format(rs.Fields("MT_TVA").Value * -100, "000000000000")
            End If
            buf = buf & Space(19)
            buf = buf & "00000"
            ' ecrire la ligne 
            sb.AppendLine(buf)
        End If

        If rs.Fields("MT_VTE").Value <> 0 Then
            buf = "M"
            buf = buf & rs.Fields("CPT_VTE").Value
            buf = buf & JNL_VENTE
            buf = buf & "001"
            buf = buf & Date2JJMMAA(TE_FIN.Value.ToString) 'JJMMAA
            buf = buf & " "

            champ = "PRESTATION A " & Format(rs.Fields("taux").Value, "0.00")
            buf = buf & SetLength(champ, 20)
            buf = buf & "C"
            If rs.Fields("MT_VTE").Value > 0 Then
                buf = buf & "+"
                buf = buf & Format(rs.Fields("MT_VTE").Value * 100, "000000000000")
            Else
                buf = buf & "-"
                buf = buf & Format(rs.Fields("MT_VTE").Value * -100, "000000000000")
            End If
            buf = buf & Space(19)
            buf = buf & "00000"
            ' ecrire la ligne 
            sb.AppendLine(buf)
        End If

    End Sub

    Private Function Date2JJMMAA(szDate As String) As String
        ' convertir une date JJ/MM/AAAA à JJMMAA
        Date2JJMMAA = szDate.Substring(0, 2) & szDate.Substring(3, 2) & szDate.Substring(8, 2)
    End Function

    Private Function SetLength(champ As String, longueur As Integer) As String

        ' convertir une chaine à la bonne longueur 
        If champ.Length > longueur Then
            ' en tronquant
            SetLength = champ.Substring(0, longueur)
        Else
            ' en complétant d'espaces
            SetLength = champ & Space(longueur - champ.Length)
        End If

    End Function

End Class